package com.system.dao;

import com.am.base.BaseDao;
import com.system.uitls.CommonUtil;
import com.system.uitls.IdUtils;
import com.system.uitls.PinyingUtil;
import java.io.FileInputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletContext;
import net.buffalo.request.RequestContext;
import org.apache.commons.logging.Log;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

public class DrugDao extends BaseDao
{
  public List<Map<String, Object>> getDrugAutoData(String keyword, String campus)
  {
    String sql = "SELECT id,name,drugstore_num,spec,pin_yin,supply_price FROM sys_drug WHERE (name like '%" + keyword + "%' OR pin_yin like '%" + keyword + "%') AND drugstore_num>0 AND storeroom=?";
    return getSimpleJdbc().queryForList(sql, new Object[] { campus });
  }

  public Map<String, String> importXls(Map<String, Object> map)
  {
    String type = CommonUtil.nullToEmptyString(map.get("type"));
    String filePath = CommonUtil.nullToEmptyString(map.get("filePath"));
    String rate = CommonUtil.nullToEmptyString(map.get("rate"));
    SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    String date = df.format(new Date());
    String userId = CommonUtil.nullToEmptyString(map.get("userId"));
    String userName = CommonUtil.nullToEmptyString(map.get("userName"));
    String userCampus = CommonUtil.nullToEmptyString(map.get("userCampus"));
    Map message = new HashMap();
    filePath = RequestContext.getContext().getServletContext().getRealPath("/WEB-INF/upload") + "/" + filePath;
    String sql = "INSERT INTO sys_drug(id,name,spec,vender,approval_number,unit,storeroom_num,supply_price,batch_number_valid,pin_yin,drugstore_num,is_own,type,storeroom,drugstore_min,storeroom_min) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    String sql_update = "";
    String sql_out = "";
    String sql_history = "";
    try {
      Workbook wookbook = null;
      try
      {
        wookbook = new HSSFWorkbook(new FileInputStream(filePath));
      }
      catch (Exception ex)
      {
        wookbook = new XSSFWorkbook(new FileInputStream(filePath));
      }

      Sheet sheet = wookbook.getSheetAt(0);
      int rows = sheet.getPhysicalNumberOfRows();
      List list = new ArrayList();
      List list_update = new ArrayList();
      List list_out = new ArrayList();
      List list_history = new ArrayList();

      String sys_out_storage_id = IdUtils.getSequence(getJdbc(), "sys_out_storage_id");
      sql_out = "INSERT INTO sys_out_storage(id,create_time,user_id,user_name,place,campus,notes) VALUES(?,?,?,?,?,?,?)";
      list_out.add(new Object[] { sys_out_storage_id, date, userId, userName, Integer.valueOf(1), userCampus, "入库" });

      for (int i = 1; i < rows; i++) {
        Row row = sheet.getRow(i);
        row.getCell(5).setCellType(1);
        if (row != null) {
          int cells = row.getLastCellNum();
          Object[] param = new Object[16];
          for (int j = 0; j < cells; j++)
          {
            Cell cell = row.getCell(j);
            if (cell == null)
              continue;
            switch (cell.getCellType()) {
            case 2:
              break;
            case 0:
              if (HSSFDateUtil.isCellDateFormatted(cell))
                param[(j + 1)] = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
              else {
                param[(j + 1)] = Double.valueOf(cell.getNumericCellValue());
              }
              break;
            case 1:
              param[(j + 1)] = cell.getStringCellValue();
            }

          }

          param[0] = IdUtils.getSequence(getJdbc(), "sys_drug_id");
          param[9] = new PinyingUtil().converterToFirstSpell(param[1].toString());
          param[10] = "0";
          param[11] = "1";
          param[12] = type;
          param[13] = userCampus;
          param[14] = Integer.valueOf(0);
          param[15] = Integer.valueOf(0);

          DecimalFormat decimalFormat = new DecimalFormat("#.00");
          param[7] = decimalFormat.format(Double.parseDouble(param[7].toString()) * Integer.parseInt(rate) / 100.0D);

          String sys_drug_history_id = IdUtils.getSequence(getJdbc(), "sys_drug_history_id");
          Object[] params = { param[1].toString().trim(), param[3].toString().trim(), param[7] };
          Map map_find = findSameDrug(params);
          if (map_find != null) {
            sql_update = "UPDATE sys_drug SET storeroom_num = ? WHERE id = ?";
            param[6] = Integer.valueOf(Integer.parseInt(param[6].toString()) + Integer.parseInt(map_find.get("storeroom_num").toString()));
            Object id = map_find.get("id");
            list_update.add(new Object[] { param[6], id });

            sql_history = "INSERT INTO sys_drug_history(id,drug_id,out_storage_id,quantity,status,type) VALUES(?,?,?,?,?,?)";
            list_history.add(new Object[] { sys_drug_history_id, id, sys_out_storage_id, param[6], "1", "5" });
          }
          else
          {
            sql_history = "INSERT INTO sys_drug_history(id,drug_id,out_storage_id,quantity,status,type) VALUES(?,?,?,?,?,?)";
            list_history.add(new Object[] { sys_drug_history_id, param[0], sys_out_storage_id, param[6], "1", "5" });

            list.add(param);
            sql = "INSERT INTO sys_drug(id,name,spec,vender,approval_number,unit,storeroom_num,supply_price,batch_number_valid,pin_yin,drugstore_num,is_own,type,storeroom,drugstore_min,storeroom_min) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
          }

          if (list.size() == 100) {
            getSimpleJdbc().batchUpdate(sql, list);

            list.clear();
          }
          if (list_update.size() == 100) {
            getSimpleJdbc().batchUpdate(sql_update, list_update);

            list_update.clear();
          }
          if (list_history.size() == 100) {
            getSimpleJdbc().batchUpdate(sql_history, list_history);

            list_history.clear();
          }
        }

      }

      getSimpleJdbc().batchUpdate(sql, list);
      getSimpleJdbc().batchUpdate(sql_update, list_update);
      getSimpleJdbc().batchUpdate(sql_out, list_out);
      getSimpleJdbc().batchUpdate(sql_history, list_history);
      logger.info("com.system.dao.DrugDao -->importXls 导入到数据库成功");
      message.put("type", "success");
      message.put("value", "导入成功");
    } catch (Exception e) {
      message.put("type", "fail");
      message.put("value", "导入失败");
      logger.error("com.system.dao.DrugDao -->importXls 导入到数据库失败\n" + e.getMessage());
    }

    return message;
  }

  public Map<String, Object> findSameDrug(Object[] param)
  {
    String sql = "SELECT id, storeroom_num FROM sys_drug WHERE name = ? and vender = ? and supply_price = ?";
    List list = getSimpleJdbc().queryForList(sql, param);
    if (list.isEmpty()) {
      return null;
    }
    Map map = (Map)list.get(0);
    return map;
  }

  public Map<String, Object> findDrug(String id, String value)
  {
    Map map = null;
    if (Integer.parseInt(value) == 0) {
      String sql = "SELECT name,vender,spec,drugstore_num AS drug_num,is_own,drugstore_min AS drug_min FROM sys_drug WHERE id=?";
      map = getSimpleJdbc().queryForMap(sql, new Object[] { id });
    }
    if (Integer.parseInt(value) == 1) {
      String sql = "SELECT name,vender,spec,storeroom_num AS drug_num,is_own,storeroom_min AS drug_min FROM sys_drug WHERE id=?";
      map = getSimpleJdbc().queryForMap(sql, new Object[] { id });
    }
    String drug_num = map.get("drug_num").toString();
    String drug_min = map.get("drug_min").toString();
    map.put("drug_num", drug_num);
    map.put("drug_min", drug_min);
    return map;
  }

  public Map<String, String> update(Map<String, Object> map)
  {
    String id = CommonUtil.nullToEmptyString(map.get("id"));
    String value = CommonUtil.nullToEmptyString(map.get("value"));
    Map result = new HashMap();
    List sqlList = new ArrayList();
    List params = new ArrayList();
    String drugstore_min = null;
    String storeroom_min = null;
    if (Integer.parseInt(value) == 0) {
      drugstore_min = CommonUtil.nullToEmptyString(map.get("drugstore_min"));
    }
    if (Integer.parseInt(value) == 1) {
      storeroom_min = CommonUtil.nullToEmptyString(map.get("storeroom_min"));
    }
    String is_own = CommonUtil.nullToEmptyString(map.get("is_own"));
    try {
      if (Integer.parseInt(value) == 0) {
        sqlList.add("UPDATE sys_drug SET drugstore_min=? ,is_own=? WHERE id=?");
        params.add(new Object[] { drugstore_min, is_own, id });
      }
      if (Integer.parseInt(value) == 1) {
        sqlList.add("UPDATE sys_drug SET storeroom_min=? ,is_own=? WHERE id=?");
        params.add(new Object[] { storeroom_min, is_own, id });
      }

      batchInTransaction(sqlList, params);
      result.put("type", "success");
      result.put("value", "修改成功");
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
      result.put("type", "fail");
      result.put("value", "修改失败");
    }

    return result;
  }

  public Map<String, String> insertResult(Map<String, Object> map)
  {
    Map result = new HashMap();
    List sqlList = new ArrayList();
    List params = new ArrayList();

    String value = CommonUtil.nullToEmptyString(map.get("value"));
    String style = CommonUtil.nullToEmptyString(map.get("style"));
    SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    String date = df.format(new Date());
    String userId = CommonUtil.nullToEmptyString(map.get("userId"));
    String userName = CommonUtil.nullToEmptyString(map.get("userName"));
    String userCampus = CommonUtil.nullToEmptyString(map.get("userCampus"));
    String type = CommonUtil.nullToEmptyString(map.get("type"));
    String notes = CommonUtil.nullToEmptyString(map.get("notes"));
    String approve_user_id = CommonUtil.nullToEmptyString(map.get("approve_user_id"));
    String approve_user_name = CommonUtil.nullToEmptyString(map.get("approve_user_name"));
    String outCompany = CommonUtil.nullToEmptyString(map.get("outCompany"));

    if (CommonUtil.nullToEmptyString(map.get("arr")) == "[]") {
      String sys_out_storage_id = IdUtils.getSequence(getJdbc(), "sys_out_storage_id");
      String sys_approve_id = IdUtils.getSequence(getJdbc(), "sys_approve_id");

      sqlList.add("INSERT INTO sys_out_storage(id,create_time,user_id,user_name,place,campus,notes) VALUES(?,?,?,?,?,?,?)");
      params.add(new Object[] { sys_out_storage_id, date, userId, userName, value, userCampus, notes });

      sqlList.add("INSERT INTO sys_approve(id,approve_user_id,approve_user_name,type,status,out_storage_id) VALUES(?,?,?,?,?,?)");
      params.add(new Object[] { sys_approve_id, approve_user_id, approve_user_name, type, "0", sys_out_storage_id });

      batchInTransaction(sqlList, params);
      result.put("type", "success");
      result.put("value", "成功");

      return result;
    }

    Integer[] idArr = (Integer[])null;
    try
    {
      idArr = (Integer[])map.get("arr");
    }
    catch (Exception e) {
      result.put("type", "fail");
      result.put("value", "没有选中药品");
      return result;
    }
    try
    {
      if ("".equals(style))
      {
        String sys_out_storage_id = IdUtils.getSequence(getJdbc(), "sys_out_storage_id");
        String sys_approve_id = IdUtils.getSequence(getJdbc(), "sys_approve_id");

        for (Integer idd : idArr) {
          String id = idd.toString();
          String numStr = CommonUtil.nullToEmptyString(map.get("name" + id));

          String sys_drug_history_id = IdUtils.getSequence(getJdbc(), "sys_drug_history_id");
          sqlList.add("INSERT INTO sys_drug_history(id,drug_id,out_storage_id,quantity,status,type) VALUES(?,?,?,?,?,?)");
          params.add(new Object[] { sys_drug_history_id, id, sys_out_storage_id, numStr, "0", type });
        }

        sqlList.add("INSERT INTO sys_out_storage(id,create_time,user_id,user_name,place,campus,notes) VALUES(?,?,?,?,?,?,?)");
        params.add(new Object[] { sys_out_storage_id, date, userId, userName, value, userCampus, notes });

        sqlList.add("INSERT INTO sys_approve(id,approve_user_id,approve_user_name,type,status,out_storage_id) VALUES(?,?,?,?,?,?)");
        params.add(new Object[] { sys_approve_id, approve_user_id, approve_user_name, type, "0", sys_out_storage_id });
      }
      else
      {
        String sys_out_storage_id = IdUtils.getSequence(getJdbc(), "sys_out_storage_id");
        String sys_approve_id = IdUtils.getSequence(getJdbc(), "sys_approve_id");

        for (Integer idd : idArr) {
          String id = idd.toString();
          String numStr = CommonUtil.nullToEmptyString(map.get("name" + id));

          String sys_drug_history_id = IdUtils.getSequence(getJdbc(), "sys_drug_history_id");
          sqlList.add("INSERT INTO sys_drug_history(id,drug_id,out_storage_id,quantity,status,type) VALUES(?,?,?,?,?,?)");
          params.add(new Object[] { sys_drug_history_id, id, sys_out_storage_id, numStr, "0", type });
        }

        if (Integer.parseInt(type) == 3)
        {
          sqlList.add("INSERT INTO sys_out_storage(id,create_time,user_id,user_name,place,campus,notes,outCompany) VALUES(?,?,?,?,?,?,?,?)");
          params.add(new Object[] { sys_out_storage_id, date, userId, userName, "1", userCampus, notes, outCompany });
        }
        else {
          sqlList.add("INSERT INTO sys_out_storage(id,create_time,user_id,user_name,place,campus,notes) VALUES(?,?,?,?,?,?,?)");
          params.add(new Object[] { sys_out_storage_id, date, userId, userName, "1", userCampus, notes });
        }

        sqlList.add("INSERT INTO sys_approve(id,approve_user_id,approve_user_name,type,status,out_storage_id) VALUES(?,?,?,?,?,?)");
        params.add(new Object[] { sys_approve_id, approve_user_id, approve_user_name, type, "0", sys_out_storage_id });
      }
      batchInTransaction(sqlList, params);
      result.put("type", "success");
      result.put("value", "成功");
    }
    catch (Exception ex) {
      ex.printStackTrace();
      result.put("type", "fail");
      result.put("value", "失败");
    }

    return result;
  }
}